package com.sys.utils;

import java.io.Serializable;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

/**
 * @author jack
 *
 */
public class DB {
	public static Connection conn;
	
	public static Connection getCon() throws SQLException {
		//use jdbc
//		try {
//			Class.forName("com.mysql.jdbc.Driver");
//			String url ="jdbc:mysql://234324.com:3329/sdf?useUnicode=true&amp;characterEncoding=UTF8";
//			//myDB为数据库名
//			conn= DriverManager.getConnection(url, "234", "234");
//		} catch (Exception e) {
//			e.printStackTrace();
//		}
		
		if (conn == null || conn.isClosed()) {
			ApplicationContext ctx = new ClassPathXmlApplicationContext("applicationSpringContext-hibernate-config.xml");
			DataSource ds = (DataSource) ctx.getBean("dataSource");
			conn = ds.getConnection();
		} else {
		}
		return conn;
	}
	
	@SuppressWarnings("unchecked")
	public static void setParams (PreparedStatement query, Object... queryParams) throws SQLException {
		if (queryParams != null) {
			for (int i = 0, j = 1; i < queryParams.length; i++) {
				Object q = queryParams[i];
				if (q != null && q instanceof List) {
					List<Serializable> c = (List<Serializable>) q;
					for (Serializable serializable : c) {
						query.setObject(j++, serializable);
					}
				} else {
					query.setObject(j++, q);
				}

			}
		}
	}
	
	
	public static int excuteSQL(String sql, Object... queryParams) throws SQLException {
		PreparedStatement query = getCon().prepareStatement(sql);
		// query.setHint("QUERY_RESULT_CACHE", "false");
		setParams(query, queryParams);
		return query.executeUpdate();
	}
	
	public static int getLastInsertINTId() throws SQLException {
		int id = getOne("SELECT LAST_INSERT_ID()");
		return id;
	}
	
	
	@SuppressWarnings("unchecked")
	public static <X> X getOne(String sql, Object... queryParams) throws SQLException  {
		PreparedStatement query = getCon().prepareStatement(sql);
		setParams(query, queryParams);
		ResultSet res = query.executeQuery();
		if (res.next()) {
			return (X) res.getObject(1);
		} else {
			return null;
		}
	}

	@SuppressWarnings("unchecked")
	public static <X> X getRowValue(Object[] row, int rowIndex, Class<X> type) {
		Object value = row[rowIndex];
		if (value == null) {
			return null;
		}
		
		if (type.equals(String.class)) {
			return (X) new String(value.toString());
		}else if (value instanceof BigDecimal) {
			BigDecimal decimal = (BigDecimal) value;
			if (type.equals(Double.class)) {
				return (X) new Double(decimal.toString());
			} else if (type.equals(Float.class)) {
				return (X) new Float(decimal.toString());
			} else if (type.equals(Long.class)) {
				return (X) new Long(decimal.toString());
			}else if (type.equals(Integer.class)) {
				return (X) new Integer(decimal.toString());
			}else if (type.equals(Short.class)) {
				return (X) new Short(decimal.toString());
			}else if (type.equals(Byte.class)) {
				return (X) new Byte(decimal.toString());
			}else if (type.equals(Boolean.class)) {
				return (X) new Boolean(decimal.byteValue() ==1);
			} else if (type.equals(BigDecimal.class)) {
				return (X) decimal;
			} else {
				return (X) new Double(decimal.toString());
			}
		} else if (value instanceof BigInteger) {
			BigInteger integer = (BigInteger) value;
			if (type.equals(Long.class)) {
				return (X) new Long(integer.toString());
			}else if (type.equals(Integer.class)) {
				return (X) new Integer(integer.toString());
			}else if (type.equals(Short.class)) {
				return (X) new Short(integer.toString());
			}else if (type.equals(Byte.class)) {
				return (X) new Byte(integer.toString());
			}else if (type.equals(Boolean.class)) {
				return (X) new Boolean(integer.byteValue() ==1);
			} else if (type.equals(BigInteger.class)) {
				return (X) integer;
			} else {
				return (X) new Long(integer.toString());
			}
		}
		
		
		return (X) value;
	}
	
	public static String toSQL(String ql, Object... args) {
		String temp = new String(ql);
		int i = 1;
		for (Object serializable : args) {
			if (serializable == null) {
				temp = temp.replace("?" + i, "null");
			} else if (serializable instanceof Date) {
				temp = temp.replace("?" + i, "'" + StringUtil.formatFullDate((Date)serializable) + "'");
			} else if (serializable instanceof Number) {
				temp = temp.replace("?" + i, ((Number)serializable).toString());
			} else {
				temp = temp.replace("?" + i, "'" + serializable.toString().replace("'", "\\'")+ "'");
			}
			i ++;
		}
		return temp;
	}
	
	public static String getOrderBy(boolean isASC) {
		return (isASC? " ASC" : " DESC");
	}
	
	
	public static <X> String listToString(Collection<X> list) {
		Iterator<X> i = list.iterator();
		if (! i.hasNext())
		    return "";

		StringBuilder sb = new StringBuilder();
		for (;;) {
		    Object e = i.next();
		    sb.append(e);
		    if (! i.hasNext())
			return sb.toString();
		    sb.append(", ");
		}
	}
	
	public static <X> String listToString(X []list, String emptyString) {
		Iterator<X> i = Arrays.asList(list).iterator();
		if (! i.hasNext())
		    return emptyString;

		StringBuilder sb = new StringBuilder();
		for (;;) {
		    Object e = i.next();
		    sb.append(e);
		    if (! i.hasNext())
			return sb.toString();
		    sb.append(", ");
		}
	}
	
	public static <X> String listToString(Collection<X> list, String emptyString) {
		Iterator<X> i = list.iterator();
		if (! i.hasNext())
		    return emptyString;

		StringBuilder sb = new StringBuilder();
		for (;;) {
		    Object e = i.next();
		    sb.append(e == null ? null : StringUtil.sqlQuote(e.toString()));
		    
		    if (! i.hasNext())
			return sb.toString();
		    sb.append(", ");
		}
	}
	
	
	public static ArrayList<Bean> query(String sql, Object ...queryParams) throws SQLException {
		PreparedStatement query = getCon().prepareStatement(sql);
		setParams(query, queryParams);
		ResultSet res = query.executeQuery();
		ArrayList<Bean> ls = new ArrayList<Bean>();
		while (res.next()) {
			Bean b = new Bean();
			
			for (int i = 0; i < query.getMetaData().getColumnCount(); i++) {
				String field = query.getMetaData().getColumnName(i + 1);
				Serializable v = (Serializable) res.getObject(i + 1);
				b.set(field, v);
			}
			ls.add(b);
		}
		return ls;
	}
	
	public static <T> List<T> getCol(List<Bean> bs, String key) {
		List<T> ls = new ArrayList<T>(bs.size());
		for (Bean bean : bs) {
			T t = bean.get(key);
			ls.add(t);
		}
		return ls;
	}
	
	public static void applykey(List<Bean> base, List<Bean>as, String idField, String copyKey, String asKey) {
		for (Bean bean : as) {
			Object id1 = bean.get(idField);
			if (id1 == null) continue;
			for (Bean b : base) {
				Object id2 = b.get(idField);
				if (id2 == null) continue;
				
				if (id1.equals(id2)) {
					b.set(asKey, bean.get(copyKey));
				}
			}
		}
	}
	
	@SuppressWarnings("unchecked")
	public static <T> List<T> getCol(String sql, Object ...queryParams) throws SQLException {
		PreparedStatement query = getCon().prepareStatement(sql);
		setParams(query, queryParams);
		ResultSet res = query.executeQuery();
		ArrayList<T> ls = new ArrayList<T>();
		while (res.next()) {
				T v = (T) res.getObject( 1);
				ls.add(v);
		}
		return ls;
	}
	
	/**
	 * 获取查询的第一条, 如果查询小于一条,返回null.
	 * @param sql
	 * @param fields
	 * @param queryParams
	 * @return
	 * @throws SQLException 
	 */
	public static Bean queryOne(String sql, Object ...queryParams) throws SQLException {
		ArrayList<Bean> bs = query(sql,queryParams);
		if (bs.isEmpty()) return null;
		return bs.get(0);
	}
	
	
	public static String format(String sql, Bean assign) {
		StringBuilder sb = new StringBuilder(sql.length() + 500);
		int fromIndex = 0;
		while (true) {
			int keyStart = sql.indexOf("{", fromIndex), keyEnd = sql.indexOf("}", fromIndex);
			if (keyStart == -1 || keyEnd == -1) {
				sb.append(sql.substring(fromIndex));
				break;
			} 
			
			String prefix = sql.substring(fromIndex, keyStart);
			String key = sql.substring(keyStart + 1, keyEnd);
			
			sb.append(prefix);
			
			try {
				sb.append(execMacro(key, assign));
			} catch (RuntimeException e) {
				System.out.println("getkey:" + key);
				throw e;
			}
			fromIndex = keyEnd + 1;
		}
		return sb.toString();
	}
	
	/**
	 * 执行宏替换.
	 * @param key
	 * @param assign
	 * @return
	 */
	private static String execMacro(String key, Bean assign) {
		String iss[] = key.split("\\?", 2);
		Object v = null;
		String q = null, maro = null;
		q = iss[0];
		if (iss.length ==2) {
			maro = iss[1].trim();
		}
		if (!assign.getP().containsKey(q)) {
			throw new RuntimeException("not found key = " + q + "!");
		}
		
		v = assign.get(q);
		if (maro == null) {
			if (v == null) return null;
			return v.toString();
		} else if (v == null){
			return "null";
		} else if (maro.equals("s")) {
			String s = (String) v;
			return StringUtil.sqlQuote(s);
		} else if (maro.equals("d")) {
			Date d = (Date) v;
			return StringUtil.sqlQuote(StringUtil.formatFullDate(d));
		} else if (maro.equals("h")){
			String s = (String) v;
			return StringUtil.sqlQuote(StringUtil.htmlEncode(s));
		} else {
			return v.toString();
		}
	}
	
	public static boolean containsField(String fields, String field) {
		String x = "," + field, x1 = "," + field + ",";
		return fields.startsWith(x) || fields.endsWith(x) || fields.contains(x1);
	}
	
	/**
	 * 将V的属性copy到base里面。
	 * @param base
	 * @param v
	 */
	public static void apply(Bean base, Bean v) {
		for (String key : v.getP().keySet()) {
			base.set(key, v.<Serializable>get(key));
		}
	}
	
}
